let db = null;

let set_db = function (d) {
	db = d
}

let ddl_drop_table = async function (tablename) {
	let sql = "ALTER TABLE " + db.escapeId(tablename) + " RENAME TO " + db.escapeId(tablename + "_dropped_" + new Date().getTime()) + " ; "
	let result = await db.query(sql, []);
	return result;
}

let build_field_set_sql = function (field, is_drop) {
	let sql = "";
	switch (field.type) {
		case "int":
			sql += " INT";
			sql += field.length ? "(" + Number(field.length) + ")" : "";
			break;
		case "date":
			sql += " DATE";
			sql += field.length ? "(" + Number(field.length) + ")" : "";
			break;
		case "datetime":
			sql += " DATETIME";
			sql += field.length ? "(" + Number(field.length) + ")" : "";
			break;
		case "long-text":
			sql += " TEXT";
			sql += field.length ? "(" + Number(field.length) + ")" : "(65535)";
			break;
		default:
			sql += " VARCHAR";
			sql += field.length ? "(" + Number(field.length) + ")" : "(45)";
	}
	sql += is_drop ? " NULL" : (field.required ? " NOT NULL" : " NULL");
	sql += field.default ? " DEFAULT " + db.escape(field.default) : "";
	return sql;
}

let ddl_change_table = async function (data) { 
	let diffs=data.rowsDiff;
	let origins=data.rowsOrigin;
	//改表名
	for (let diff of diffs.filter(item => item.tablename)) {
		let id=diff.id;
		let new_tablename=diff.tablename;
		let old_tablename = origins.filter(item => item.id == id)[0].tablename;
		let sql = "ALTER TABLE "+db.escapeId(old_tablename)+" RENAME TO  "+db.escapeId(new_tablename)+" ;";
		let result = await db.multiquery(sql, []);
		return result;
	}
	//改配置
	let multisql = "";
	for (let diff of diffs.filter(item => item.config)) {
		let id=diff.id;
		let q_sql = "select * from sys_datamodel where id="+db.escape(id);
		let result = await db.query(q_sql, []);
		let tablename = result.rows[0].tablename;
		let new_config = JSON.parse(diff.config);
		let old_config = JSON.parse(origins.filter(item => item.id == id)[0].config);
		let sql = "ALTER TABLE " + db.escapeId(tablename);
		let unique = "";
		//哪些字段是删除的
		for (let field of old_config.fields) {
			if (new_config.fields.filter(f => f.id == field.id).length == 0) {
				sql += " CHANGE COLUMN " + db.escapeId(field.name) + " " + db.escapeId(field.name + "_dropped_"+new Date().getTime());
				sql += build_field_set_sql(field, is_drop = true);
				sql += ",";
			}
		}
		//哪些字段是修改的
		for (let field of new_config.fields) {
			let old_change_fields = old_config.fields.filter(f => f.id == field.id);
			if (old_change_fields.length > 0) {
				let oldfield = old_change_fields[0];
				if (oldfield.name != field.name || oldfield.type != field.type || oldfield.length != field.length || oldfield.required != field.required || oldfield.default != field.default) {
					let oldname = oldfield.name;
					sql += " CHANGE COLUMN " + db.escapeId(oldfield.name) + " " + db.escapeId(field.name);
					sql += build_field_set_sql(field);
					sql += ",";
				}
				if (!oldfield.unique && field.unique) {
					unique += " ADD UNIQUE INDEX " + db.escapeId(field.name + "_UNIQUE") + " (" + db.escapeId(field.name) + " ASC) VISIBLE;"
				}
				if (oldfield.unique && !field.unique) {
					unique += " DROP INDEX " + db.escapeId(field.name + "_UNIQUE") + ";"
				}
			}
		}
		//哪些字段是新增的
		let hold_fields=new_config.fields.filter(f => old_config.fields.some(of => of.id==f.id));
		let last_field=hold_fields && hold_fields ? hold_fields[hold_fields.length-1] : null;
		for (let field of new_config.fields) {
			if (old_config.fields.filter(f => f.id == field.id).length == 0) {
				sql += " ADD COLUMN " + db.escapeId(field.name);
				sql += build_field_set_sql(field);
				sql += (last_field?(" AFTER "+ db.escapeId(last_field.name)):"");
				sql += ",";
				if (field.unique) {
					unique += " ADD UNIQUE INDEX " + db.escapeId(field.name + "_UNIQUE") + " (" + db.escapeId(field.name) + " ASC) VISIBLE;"
				}
			}
		}
		if (sql.charAt(sql.length - 1) == ",") {
			sql = sql.substr(0, sql.length - 1);
		}
		multisql += sql + unique + ";";
	}
	if (multisql.length > 0) {
		let result = await db.multiquery(multisql, []);
		return result;
	}
	else {
		return;
	}
}

let ddl_add_table = async function (tablename, config) {
	let sql = "CREATE TABLE " + db.escapeId(tablename) + " (" +
		"`id` INT NOT NULL AUTO_INCREMENT,";
	for (let field of config.fields) {
		sql += db.escapeId(field.name);
		sql += build_field_set_sql(field);
		sql += ",";
	}

	sql += "`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP," +
		"`create_user` VARCHAR(45) NULL DEFAULT NULL," +
		"`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," +
		"`update_user` VARCHAR(45) NULL DEFAULT NULL," +
		"`is_deleted` INT NULL DEFAULT '0'," +
		"PRIMARY KEY (`id`));";
	let result = await db.query(sql, []);
	return result;
}

let del_data_funcs = async function (datamodel_id, login_info, req, res) {
	let user_id = login_info.user_id;
	let sql = "update sys_data_func set is_deleted=1,update_user=? where datamodel_id=? ";
	let result = await db.del(sql, [user_id, datamodel_id]);
	return result;
}

let add_data_funcs = async function (datamodel_id, login_info, req, res) {
	let user_id = login_info.user_id;
	let sql = "insert into sys_data_func (`datamodel_id`, `name`, `create_user`) values (" + db.escape(datamodel_id) + ",'查'," + db.escape(user_id) + ");" +
		"insert into sys_data_func (`datamodel_id`, `name`, `create_user`) values (" + db.escape(datamodel_id) + ",'增'," + db.escape(user_id) + ");" +
		"insert into sys_data_func (`datamodel_id`, `name`, `create_user`) values (" + db.escape(datamodel_id) + ",'改'," + db.escape(user_id) + ");" +
		"insert into sys_data_func (`datamodel_id`, `name`, `create_user`) values (" + db.escape(datamodel_id) + ",'删'," + db.escape(user_id) + ");";
	let result = await db.multiadd(sql, []);
	return result;
}

let del_functions = async function (page_id, login_info, req, res) {
	let user_id = login_info.user_id;
	let sql = "update sys_function set is_deleted=1,update_user=? where page_id=? ";
	let result = await db.del(sql, [user_id, page_id]);
	return result;
}

let add_functions = async function (page_id, page_type_id, login_info, req, res) {
	let user_id = login_info.user_id;
	let sql = "select * from sys_page_type where id=? ";
	let result = await db.query(sql, [page_type_id]);
	let row = result.rows[0];
	let rights = row.right_set ? JSON.parse(row.right_set).rights : [];
	sql = "insert into sys_function (`page_id`, `name`, `create_user`) values (" + db.escape(page_id) + ",'访问'," + db.escape(user_id) + ");";
	if (rights) {
		for (let r of rights) {
			sql += "insert into sys_function (`page_id`, `name`, `create_user`) values (" + db.escape(page_id) + "," + db.escape(r) + "," + db.escape(user_id) + ");";
		}
	}
	result = await db.multiadd(sql, []);
	return result;
}

module.exports = { set_db, ddl_add_table, ddl_change_table, ddl_drop_table, add_data_funcs, del_data_funcs, add_functions, del_functions }